﻿using System;
using System.IO;
using System.Data;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace HiauPdm.Bll
{
    /// <summary>
    /// 产品设计查询
    /// </summary>
    public class BCpSjCx
    {
        /// <summary>
        /// 根据[上级产品类别ID]获得产品类别
        /// </summary>
        public static DataSet GetChildCplb(int iCpLbId)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery("SELECT A.ID,A.编号,A.类别名称,CAST((SELECT COUNT(1) FROM 产品类别 B,产品类别 C,产品类别 D WHERE D.ISJID=C.ID AND C.ISJID=B.ID AND B.ISJID=A.ID) AS BIT) 含子类别 FROM 产品类别 A WHERE ISNULL(A.ISJID,0)=@P0 ORDER BY A.编号", iCpLbId);
            }
        }

        /// <summary>
        /// 查询产品类别
        /// </summary>
        public static DataSet QueryCplb(int iSjLbSjId)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery("SELECT A.ID,A.编号,A.大类名称,'['+B.类别名称+'] '+A.类别名称 类别名称 FROM 产品类别 A,产品类别 B WHERE A.ISJID=B.ID AND B.ISJID=@P0 ORDER BY A.编号", iSjLbSjId);
            }
        }

        /// <summary>
        /// 查询产品类别
        /// </summary>
        public static DataSet QueryCplb(string sWhere)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery(string.Format("SELECT A.ID,A.编号,A.大类名称,'['+B.类别名称+'] '+A.类别名称 类别名称 FROM 产品类别 A,产品类别 B WHERE A.ISJID=B.ID AND LEN(A.编号)=9 AND {0} ORDER BY A.编号", BGlobal.BuildLike(sWhere, "A.类别名称", "B.类别名称")));
            }
        }

        /// <summary>
        /// 查询产品设计代号
        /// </summary>
        public static DataSet QueryCpSjh(string sWhere)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery(string.Format("SELECT A.ID,A.代号,A.名称,A.规格型号,A.单位,A.备注,A.登记人,CASE (SELECT COUNT(1) FROM 产品明细 B WHERE B.零件ID=A.ID) WHEN 0 THEN '' ELSE '有' END 引用 FROM 产品设计 A WHERE {0} ORDER BY A.代号", BGlobal.BuildLike(sWhere, "A.代号", "A.名称", "A.规格型号")));
            }
        }

        /// <summary>
        /// 获得产品设计的登记人
        /// </summary>
        public static DataSet GetCpSjDjr()
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery("SELECT 登记人 FROM 产品设计 GROUP BY 登记人");
            }
        }

        /// <summary>
        /// 根据[查询条件]查询产品设计总记录数
        /// </summary>
        public static DataSet GetCpSjCountByCx(string sLb, int iZt, string sDjr, string sWhere, int iCpSjIdYy, int iWgjIdYy)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery(string.Format("SELECT COUNT(1) FROM 产品设计 A WHERE (@P0='' OR A.类别=@P0) AND (@P1=-1 OR A.状态=@P1) AND (@P2='' OR A.登记人=@P2) AND {0} AND (@P3=-1 OR EXISTS(SELECT 1 FROM 产品明细 B WHERE B.产品ID=A.ID AND B.零件ID=@P3)) AND (@P4=-1 OR EXISTS(SELECT 1 FROM 产品外购 B WHERE B.产品ID=A.ID AND B.购件ID=@P4))", BGlobal.BuildLike(sWhere, "A.代号", "A.名称", "A.规格型号")), sLb, iZt, sDjr, iCpSjIdYy, iWgjIdYy);
            }
        }

        /// <summary>
        /// 根据[查询条件]查询产品设计
        /// </summary>
        public static DataSet GetCpSjByCx(string sLb, int iZt, string sDjr, string sWhere, int iCpSjIdYy, int iWgjIdYy, int iPageSize, int iPageIndex)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery(string.Format("SELECT TOP {0} A.ID,A.代号,A.名称,A.规格型号,A.备注,A.登记人,(SELECT COUNT(1) FROM 产品文档 B WHERE B.产品ID=A.ID) 文档,(SELECT COUNT(1) FROM 产品明细 B WHERE B.产品ID=A.ID) 零部件,(SELECT COUNT(1) FROM 产品外购 B WHERE B.产品ID=A.ID) 外购件,CASE (SELECT COUNT(1) FROM 产品明细 B WHERE B.零件ID=A.ID) WHEN 0 THEN '' ELSE '有' END 引用,CASE A.状态 WHEN 0 THEN '未提交' WHEN 1 THEN '已提交' ELSE '已审核' END 状态,CASE WHEN (SUBSTRING(A.代号,5,1) BETWEEN 1 AND 4) THEN 2 WHEN (SUBSTRING(A.代号,5,1) BETWEEN 5 AND 6) THEN 1 ELSE 0 END 大类 FROM 产品设计 A WHERE (@P0='' OR A.类别=@P0) AND (@P1=-1 OR A.状态=@P1) AND (@P2='' OR A.登记人=@P2) AND {2} AND (@P3=-1 OR EXISTS(SELECT 1 FROM 产品明细 B WHERE B.产品ID=A.ID AND B.零件ID=@P3)) AND (@P4=-1 OR EXISTS(SELECT 1 FROM 产品外购 B WHERE B.产品ID=A.ID AND B.购件ID=@P4)) AND A.ID NOT IN(SELECT TOP {1} B.ID FROM 产品设计 B WHERE (@P0='' OR B.类别=@P0) AND (@P1=-1 OR B.状态=@P1) AND (@P2='' OR B.登记人=@P2) AND {3} AND (@P3=-1 OR EXISTS(SELECT 1 FROM 产品明细 C WHERE C.产品ID=B.ID AND C.零件ID=@P3)) AND (@P4=-1 OR EXISTS(SELECT 1 FROM 产品外购 C WHERE C.产品ID=B.ID AND C.购件ID=@P4)) ORDER BY B.代号) ORDER BY A.代号", iPageSize, iPageSize * (iPageIndex - 1), BGlobal.BuildLike(sWhere, "A.代号", "A.名称", "A.规格型号"), BGlobal.BuildLike(sWhere, "B.代号", "B.名称", "B.规格型号")), sLb, iZt, sDjr, iCpSjIdYy, iWgjIdYy);
            }
        }

        /// <summary>
        /// 根据[查询条件]查询产品设计
        /// </summary>
        public static DataSet GetCpSjCxTree(string sLb, int iZt, string sDjr, string sWhere, int iCpSjIdYy, int iWgjIdYy)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery(string.Format("SELECT A.ID,A.代号,A.名称,A.规格型号,CAST((SELECT COUNT(1) FROM 产品明细 C WHERE C.产品ID=A.ID) AS BIT) 含零部件,CAST((SELECT COUNT(1) FROM 产品外购 C WHERE C.产品ID=A.ID) AS BIT) 含外购件 FROM 产品设计 A WHERE (SUBSTRING(A.代号,5,1) BETWEEN 1 AND 6) AND (@P0='' OR A.类别=@P0) AND (@P1=-1 OR A.状态=@P1) AND (@P2='' OR A.登记人=@P2) AND {0} AND (@P3=-1 OR EXISTS(SELECT 1 FROM 产品明细 B WHERE B.产品ID=A.ID AND B.零件ID=@P3)) AND (@P4=-1 OR EXISTS(SELECT 1 FROM 产品外购 B WHERE B.产品ID=A.ID AND B.购件ID=@P4)) ORDER BY A.代号", BGlobal.BuildLike(sWhere, "A.代号", "A.名称", "A.规格型号")), sLb, iZt, sDjr, iCpSjIdYy, iWgjIdYy);
            }
        }

        /// <summary>
        /// 根据[产品设计ID]查询子产品设计
        /// </summary>
        public static DataSet GetCpSjCxTree(int iCpSjId)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery("SELECT A.ID,A.代号,A.名称,A.规格型号,CAST((SELECT COUNT(1) FROM 产品明细 C WHERE C.产品ID=A.ID) AS BIT) 含零部件,CAST((SELECT COUNT(1) FROM 产品外购 C WHERE C.产品ID=A.ID) AS BIT) 含外购件 FROM 产品设计 A, 产品明细 B WHERE A.ID=B.零件ID AND B.产品ID=@P0 ORDER BY A.代号", iCpSjId);
            }
        }

        /// <summary>
        /// 根据[产品设计ID]查询产品设计
        /// </summary>
        public static DataSet GetCpSjCxByCpSjId(int iCpSjId)
        {
            using (DSql da = new DSql(BGlobal.DbConnString))
            {
                return da.ExecuteQuery("SELECT A.ID,A.代号,A.类别,A.名称,A.规格型号,A.单位,A.备注,A.登记人,A.编辑人,A.编辑时间,CASE WHEN (SUBSTRING(A.代号,5,1) BETWEEN 1 AND 4) THEN 2 WHEN (SUBSTRING(A.代号,5,1) BETWEEN 5 AND 6) THEN 1 ELSE 0 END 大类 FROM 产品设计 A WHERE A.ID=@P0", iCpSjId);
            }
        }

        /// <summary>
        /// 导出产品设计明细表
        /// </summary>
        public static void ExportCpSjMxb(Page tPage, string sDh, string sMc, string sGgxh, DataSet dsLbj, DataSet dsWgj)
        {
            if (tPage == null || dsLbj == null || dsWgj == null || (dsLbj.Tables[0].Rows.Count <= 0 && dsWgj.Tables[0].Rows.Count <= 0)) { return; }
            string sFileName = string.Format("Ls{0}{1}.tmp", tPage.Session.SessionID, DateTime.Now.ToString("yyyyMMddHHmmssffffff"));
            using (DExcel dExcel = new DExcel(tPage.Server.MapPath("~/ExcelMb/CpSjMxb.xls")))
            {
                dExcel.WriteCellValues(1, 1, string.Format("{0}{1}", sMc, sGgxh));
                dExcel.WriteCellValues(2, 1, string.Format("代号：{0}", sDh));
                dExcel.WriteCellValues(2, 3, string.Format("制表：{0}  {1}", tPage.Session["RealName"], DateTime.Now.ToString("yyyy-MM-dd HH:mm")));
                if (dsLbj.Tables[0].Rows.Count > 0 && !dExcel.WriteDataTable(4, 1, dsLbj.Tables[0], "代号", "名称", "规格型号", "单位", "装入数量", "装入代号")) { return; }
                if (dsWgj.Tables[0].Rows.Count > 0 && !dExcel.WriteDataTable(4 + dsLbj.Tables[0].Rows.Count, 1, dsWgj.Tables[0], "代号", "名称", "规格型号", "单位", "装入数量", "装入代号")) { return; }
                if (!dExcel.OutputExcelFile(string.Format("{0}{1}", BGlobal.AttachmentPath, sFileName))) { return; }
            }
            tPage.Response.Redirect("DownloadFile.aspx?id=" + BGlobal.Encrypt(string.Format("{0}|[{1}]{2}{3}_明细表.xls", sFileName, sDh, sMc, sGgxh), "Download"));
        }
    }
}
